package com.bdqn.oa.dao.impl;

import com.bdqn.oa.dao.AnnouncementDao;
import com.bdqn.oa.entity.Announcement;
import com.bdqn.oa.utils.DruidUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import java.sql.SQLException;
import java.util.Date;
import java.util.List;

public class AnnouncementDaoImpl implements AnnouncementDao {
    QueryRunner queryRunner = new QueryRunner();
    /**
     * 获取所有的公告
     *
     * @return 返回公告List集合
     */
    @Override
    public List<Announcement> listAnnouncementAll() throws SQLException {
        String sql = "select `id`,`title`,`content`,`datetime`,`employee_name` as employeename  from announcement; ";
        return queryRunner.query(DruidUtils.getConnection(),sql,new BeanListHandler<Announcement>(Announcement.class));

    }

    /**
     * 通过公告标题查询公告
     *
     * @param title
     * @return
     */
    @Override
    public Announcement getTitleQueryAnnouncement(String title) throws SQLException {
        String sql = "select `id`,`title`,`content`,`datetime`,`employee_name` as employeename from announcement where title = ?;";
        return queryRunner.query(DruidUtils.getConnection(),sql,new BeanHandler<Announcement>(Announcement.class),title);
    }

    /**
     * 通过大概内容查询有关的公告
     *
     * @param content
     * @return
     */
    @Override
    public List<Announcement> listCotentQueryAnnouncement(String content) throws SQLException {
        String sql = "select `id`,`title`,`content`,`datetime`,`employee_name` as employeename from announcement where content like concat('%',?,'%');";
        return queryRunner.query(DruidUtils.getConnection(),sql,new BeanListHandler<Announcement>(Announcement.class),content);
    }

    /**
     * 通过发起时间查询公告(只传年月日 yyyy-mm-dd)查询哪天的公告 也可以传具体的时间 看需求
     *
     * @param
     * @return
     */
    @Override
    public List<Announcement> listDateTimeQueryAnnouncement(String time) throws SQLException {
        String sql = "select `id`,`title`,`content`,`datetime`,`employee_name` as employeename from announcement where  datetime like concat('%',?,'%');";
        return queryRunner.query(DruidUtils.getConnection(),sql,new BeanListHandler<Announcement>(Announcement.class),time);
    }

    /**
     * 通过发起人的姓名查询公告
     *
     * @param name
     * @return
     */
    @Override
    public List<Announcement>listByEmployeenameQueryAnnouncement(String name) throws SQLException {
        String sql = "select`id`,`title`,`content`,`datetime`,`employee_name` as employeename from announcement where employee_Name = ?;";
        return queryRunner.query(DruidUtils.getConnection(),sql,new BeanListHandler<Announcement>(Announcement.class),name);
    }

    /**
     *   修改公告标题、内容、发起人姓名 、发布时间  只改一个也调用该方法只给公告类赋值需要修改的第一个参数是公告的ID 第一个是要修改的标题 内容 姓名或者发布时间
     *   标题内容发起人
     * @param announcement
     * @return
     * @throws SQLException
     */
    @Override
    public int updateAnnouncement( Announcement announcement) throws SQLException {
        if(announcement.getTitle() != null){
            String sql = "update announcement set title = ? where id = ?;";
            return queryRunner.update(DruidUtils.getConnection(),sql,announcement.getTitle(),announcement.getId());
        }
        if(announcement.getContent() != null){
            String sql = "update announcement set content = ? where id = ?;";
            return queryRunner.update(DruidUtils.getConnection(),sql,announcement.getContent(),announcement.getId());
        }
        if (announcement.getDatetime() !=null){
            String sql = "update announcement set datetime = ? where id = ?;";
            return queryRunner.update(DruidUtils.getConnection(),sql,announcement.getDatetime(),announcement.getId());
        }
        if (announcement.getEmployeeName() != null){
            String sql = "update announcement set datetime = ? where id = ?;";
            return queryRunner.update(DruidUtils.getConnection(),sql,announcement.getEmployeeName(),announcement.getId());
        }
        return -1;
    }

    /**
     * 通过公告id删除公告
     *
     * @param id
     * @return
     */
    @Override
    public int deleteAnnouncement(Integer id) throws SQLException {
        String sql = "delete from announcement where id = ?;";
        return queryRunner.update(DruidUtils.getConnection(),sql,id);
    }

    /**
     * 通过公告的ID数组批量删除公告
     *
     * @param id
     * @return
     */
    @Override
    public int[] deleteAnnouncement(Integer[] id) throws SQLException {
        String sql = "delete from announcement where id = ?";// 取消;
        Object [][] params = new Object[id.length][];
        for (int i = 0;i<params.length;i++){
            params[i] = new Object[]{id[i]};
        }
        return queryRunner.batch(DruidUtils.getConnection(),sql,params);
    }

    /**
     * 新增公告
     * Announcement类除了对ID不赋值 其他的属性都要赋值
     *
     * @param announcement
     * @return
     * @throws SQLException
     */
    @Override
    public int insertAnnouncement(Announcement announcement) throws SQLException {
        Object[] params = {
                announcement.getTitle(),announcement.getContent(),announcement.getDatetime(),announcement.getEmployeeName()
        };
        String sql = "insert into announcement(`title`,`content`,`datetime`,`employee_name` as employeename )VALUE(?,?,?,?);";
        return queryRunner.update(DruidUtils.getConnection(),sql,params);
    }
}

